package com.css.fxfzypg.modules.baseData.repository.impl;

import com.css.fxfzypg.constants.YNEnum;
import com.css.fxfzypg.modules.baseData.repository.AirportRepository;
import com.css.fxfzypg.modules.baseData.entity.Airport;
import com.css.fxfzypg.modules.baseData.queryParams.AirportQueryParams;
import com.css.fxfzypg.util.PlatformObjectUtils;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.PageImpl;
import org.springframework.data.domain.PageRequest;
import org.springframework.data.domain.Pageable;
import org.springframework.jdbc.core.BatchPreparedStatementSetter;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.namedparam.BeanPropertySqlParameterSource;
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;
import org.springframework.jdbc.core.namedparam.SqlParameterSource;
import org.springframework.jdbc.support.GeneratedKeyHolder;
import org.springframework.jdbc.support.KeyHolder;
import org.springframework.stereotype.Repository;

import javax.annotation.Resource;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

@Repository
public class AirportRepositoryImpl implements AirportRepository {
    @Resource
    JdbcTemplate jdbcTemplate;
    @Resource
    NamedParameterJdbcTemplate namedParameterJdbcTemplate;

//    @Override
//    public Road updateRoadById(Road road) {
//        return null;
//    }

    @Override
    public void deleteAirportsById(List<Integer> ids, Timestamp timestamp) {
        String sql = "update sys_airport set airport_status = '" + YNEnum.N + "' , airport_updatetime = '"+timestamp+"' " + " where airport_id in (:ids)";
        Map map = new HashMap();
        map.put("ids", ids);
        namedParameterJdbcTemplate.update(sql, map);
    }

    @Override
    public Airport saveAirport(Airport airport) {
        String sql = "insert into sys_airport(airport_longitude,airport_latitude,airport_name,airport_status) values(:airportLongitude,:airportLatitude,:airportName,:airportStatus)";

        SqlParameterSource source = new BeanPropertySqlParameterSource(airport);
        //拿到新插入的主键值（i）
        KeyHolder keyHolder = new GeneratedKeyHolder();
        int i = namedParameterJdbcTemplate.update(sql, source, keyHolder);
        return airport;
    }

    @Override
    public Page<Airport> getAllAirportParams(AirportQueryParams queryParams) {
        StringBuilder sql = new StringBuilder(" select  a.* from sys_airport a ");

        StringBuilder orderSql = new StringBuilder(" order by a.airport_id");

        StringBuilder whereSql = new StringBuilder(" where 1=1 ");

        if (!PlatformObjectUtils.isEmpty(queryParams.getAirportName()) ){
            whereSql.append(" and a.airport_name like '%" + queryParams.getAirportName() + "%'");
        }

//        if (!PlatformObjectUtils.isEmpty(queryParams.getRoadType()) ){
//            whereSql.append(" and r.road_type like '%" + queryParams.getRoadType() + "%'");
//        }

        String wholeSql = sql.append(whereSql).append(orderSql).toString();
        List<Airport> list = jdbcTemplate.query(wholeSql, new BeanPropertyRowMapper<>(Airport.class));
        Pageable pageable = PageRequest.of(queryParams.getCurPage() - 1, queryParams.getPageSize());

        Page<Airport> airports = new PageImpl<>(list, pageable, PlatformObjectUtils.isEmpty(list) ? 0 : list.size());

        return airports;
    }

    @Override
    public Airport getAirportById(Integer id) {
        String sql = "select * from sys_airport where airport_id = '"+id+"'";
        Airport airport = jdbcTemplate.queryForObject(sql, new BeanPropertyRowMapper<>(Airport.class));
        return airport;
    }


    @Override
    public List<Airport> getAll() {
        return jdbcTemplate.query("select * from sys_airport",new BeanPropertyRowMapper<>(Airport.class));
    }

    @Override
    public void importExcel(List<Airport> airports) {
          jdbcTemplate.batchUpdate("insert into sys_airport (airport_longitude,airport_latitude,airport_name,airport_status)values(?,?,?,?)", new BatchPreparedStatementSetter() {
            @Override
            public int getBatchSize() {
                return airports.size();
            }

            @Override
            public void setValues(PreparedStatement ps, int i) throws SQLException {
                ps.setFloat(1, airports.get(i).getAirportLongitude());
                ps.setFloat(2, airports.get(i).getAirportLatitude());
                ps.setString(3, airports.get(i).getAirportName());
                ps.setString(4, airports.get(i).getAirportStatus());
            }
        });
    }

    @Override
    public Integer countByCounty(String province, String city, String county) {
        return jdbcTemplate.queryForObject("SELECT count(*) from sys_airport where county = '" + county + "' and city = '" + city + "' and province = '" + province +"'  or (province ='"+province+"' and city ='' and county ='')  and  airport_status='1'", Integer.class);
    }

    @Override
    public Integer countByCity(String province, String city) {
        return jdbcTemplate.queryForObject("SELECT count(*) from sys_airport where city = '" + city + "' and province = '" + province + "' or (province ='"+province+"' and city ='' and county ='')  and  airport_status='1' ", Integer.class);
    }

    @Override
    public Integer countByProvince(String province) {
        return jdbcTemplate.queryForObject("SELECT count(*) from sys_airport where province = '" + province + "' and  airport_status='1'  ", Integer.class);
    }

    @Override
    public Integer countByProvinceAndYear(String provinceName, String taskYear) {
        return jdbcTemplate.queryForObject("SELECT count(t1.*) from sys_airport t1 LEFT JOIN qh_map_files t2 on t1.fileuuid = t2.uuid where t1.province = ? and (t2.update_time LIKE ? or t2.create_time LIKE ?) and t1.airport_status='1'", Integer.class, provinceName, "%" + taskYear + "%", "%" + taskYear + "%");
    }

}
